/**	
 * タイトル：	Excel工具类
 * 説明：    	
 *			
 * 会社名：	紹興日創軟件有限公司
 * 変更履歴：	2014/08/25 NIS 何
 *			新規作成
 *
 */
package com.nissoft.framework.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author NIS 何
 * @version 1.0
 */
public class ExcelUtil {

	public static final String XLS = ".xls";
	public static final String XLSX = ".xlsx";
	private final Workbook wb;
	private final File sourceFile;

	public ExcelUtil(File file) throws InvalidFormatException, IOException {
		this.sourceFile = file;
		if (file.exists()) {
			wb = WorkbookFactory.create(file);
		} else {
			String ext = FileUtil.getExtension(file.getName());
			wb = XLS.equals(ext) ? new HSSFWorkbook() : new XSSFWorkbook();
		}
	}

	/**
	 * 写入数据到当前激活的sheet
	 * 
	 * @param data
	 *            数据
	 */
	public ExcelUtil writeSheet(List<List<Object>> data) {
		return writeSheetInternal(getActiveSheet1(), data);
	}

	/**
	 * 写入数据到指定的sheet
	 * 
	 * @param sheetIndex
	 *            sheet索引
	 * @param data
	 *            数据
	 */
	public ExcelUtil writeSheet(int sheetIndex, List<List<Object>> data) {
		return writeSheetInternal(wb.getSheetAt(sheetIndex), data);
	}

	/**
	 * 写入指定数据结构(T)的数据到当前激活的sheet
	 * 
	 * @param data
	 *            数据
	 * @param fields
	 *            字段名
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public <T> ExcelUtil writeSheet(List<T> data, List<String> fields)
			throws IllegalArgumentException, IllegalAccessException {

		return writeSheet(null, data, fields);
	}

	/**
	 * 写入指定数据结构(T)的数据到当前激活的sheet
	 * 
	 * @param header
	 *            数据头部
	 * @param data
	 *            数据
	 * @param fields
	 *            字段名
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public <T> ExcelUtil writeSheet(List<Object> header, List<T> data,
			List<String> fields) throws IllegalArgumentException,
			IllegalAccessException {
		getActiveSheet1();
		return writeSheet(wb.getActiveSheetIndex(), header, data, fields);
	}

	/**
	 * 写入指定数据结构(T)的数据到当前激活的sheet
	 * 
	 * @param sheetIndex
	 *            sheet索引
	 * @param data
	 *            数据
	 * @param fields
	 *            字段名
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public <T> ExcelUtil writeSheet(int sheetIndex, List<T> data,
			List<String> fields) throws IllegalArgumentException,
			IllegalAccessException {
		return writeSheet(sheetIndex, null, data, fields);
	}

	/**
	 * 写入指定数据结构(T)的数据到当前激活的sheet
	 * 
	 * @param sheetIndex
	 *            sheet索引
	 * @param header
	 *            数据头部
	 * @param data
	 *            数据
	 * @param fields
	 *            字段名
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public <T> ExcelUtil writeSheet(int sheetIndex, List<Object> header,
			List<T> data, List<String> fields) throws IllegalArgumentException,
			IllegalAccessException {

		if (data.size() == 0) {
			return this;
		}
		T t0 = data.get(0);
		Class<?> c = t0.getClass();
		List<Field> sorted = sortUsedFields(c.getDeclaredFields(), fields);
		List<List<Object>> data1 = new ArrayList<>();
		if (header != null && header.size() != 0) {
			data1.add(header);
		}
		for (T t : data) {
			List<Object> row = new ArrayList<>();
			for (Field f : sorted) {
				row.add(f.get(t));
			}
			data1.add(row);
		}
		return writeSheet(sheetIndex, data1);
	}

	/**
	 * 读取当前激活的sheet的数据
	 * 
	 */
	public List<List<Object>> readSheet() {
		return readSheet(wb.getActiveSheetIndex());
	}

	/**
	 * 读取指定sheet的数据
	 * 
	 * @param sheetIndex
	 *            sheet索引
	 */
	public List<List<Object>> readSheet(int sheetIndex) {

		if (wb.getNumberOfSheets() - 1 < sheetIndex) {
			return null;
		}
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Iterator<Row> rIter = sheet.rowIterator();
		List<List<Object>> data = new ArrayList<>();
		while (rIter.hasNext()) {
			Row r = rIter.next();
			data.add(readRow(r));
		}
		return data;
	}

	/**
	 * 导出到目标文件
	 * 
	 * @param targetFile
	 *            目标文件
	 */
	public void output(File targetFile) throws IOException {

		FileOutputStream op = new FileOutputStream(targetFile);
		wb.write(op);
	}

	/**
	 * 导出
	 */
	public void output() throws IOException {
		output(sourceFile);
	}

	/**
	 * 获取整个工作薄
	 */
	public Workbook getWorkbook() {
		return wb;
	}

	/**
	 * 获取当前激活的sheet;如果没有,创建一个sheet
	 */
	private Sheet getActiveSheet1() {
		return wb.getNumberOfSheets() == 0 ? wb.createSheet() : wb
				.getSheetAt(wb.getActiveSheetIndex());
	}

	/**
	 * 内部写入sheet
	 */
	private ExcelUtil writeSheetInternal(Sheet sheet, List<List<Object>> data) {

		int last = sheet.getLastRowNum();
		if (last != sheet.getPhysicalNumberOfRows()) {
			last++;
		}
		for (List<Object> row : data) {
			writeRow(sheet.createRow(last++), row);
		}
		return this;
	}

	/**
	 * 写行
	 */
	private void writeRow(Row row, List<Object> data) {

		for (int i = 0; i < data.size(); i++) {
			writeCell(row.createCell(i), data.get(i));
		}
	}

	/**
	 * 写单元格
	 */
	private void writeCell(Cell cell, Object value) {

		if (value == null) {
		} else if (value instanceof Integer)
			cell.setCellValue((Integer) value);
		else if (value instanceof Double)
			cell.setCellValue((Double) value);
		else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof Date) {
			cell.setCellValue((Date) value);
		} else if (value instanceof Calendar)
			cell.setCellValue((Calendar) value);
		else if (value instanceof RichTextString)
			cell.setCellValue((RichTextString) value);
		else
			cell.setCellValue(value.toString());
	}

	/**
	 * 读行
	 */
	private List<Object> readRow(Row row) {
		if (row == null)
			return new ArrayList<Object>();
		List<Object> item = new ArrayList<>();
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Object val = readCell(row.getCell(i));
			item.add(val == null ? "" : val);
		}
		return item;
	}

	/**
	 * 读单元格
	 */
	private Object readCell(final Cell cell) {

		if (cell == null) {
			return null;
		}
		Object val = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
		case Cell.CELL_TYPE_STRING:
			val = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			val = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell))
				val = cell.getDateCellValue();
			else
				val = cell.getNumericCellValue();
			break;
		case Cell.CELL_TYPE_ERROR:
			val = cell.getErrorCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			val = cell.getCellFormula();
			break;
		}
		return val;
	}

	/**
	 * 根据字段名排序
	 */
	private List<Field> sortUsedFields(Field[] fields, List<String> fieldNames) {
		return sortUsedFields(new ArrayList<>(Arrays.asList(fields)),
				fieldNames);
	}

	/**
	 * 根据字段名排序
	 */
	private List<Field> sortUsedFields(List<Field> fields,
			List<String> fieldNames) {
		List<Field> myFields = new ArrayList<>();
		for (String name : fieldNames) {
			Iterator<Field> iter = fields.iterator();
			while (iter.hasNext()) {
				Field f = iter.next();
				if (f.getName().equals(name)) {
					f.setAccessible(true);
					myFields.add(f);
					iter.remove();
					break;
				}
			}
		}
		return myFields;
	}
}
